﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class SoHoKhauDAL
    {
        public static List<SoHoKhauPublic> LoadSoHoKhau()
        {
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau");
            return sohokhau.ToList();
        }

        public static SoHoKhauPublic LoadSoHoKhauBySoSHK(int SoSHK)
        {
            string sql = @"select * from SoHoKhau where SoSoHoKhau=" + SoSHK;
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(sql);
            //  if sohokhau == DBNull
            return sohokhau.FirstOrDefault();
        }
        public static List<SoHoKhauPublic> LoadListSoHoKhauBySoSHK(int SoSHK)
        {
            string sql = @"select * from SoHoKhau where SoSoHoKhau=" + SoSHK;
            var sohokhau = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(sql);
            //  if sohokhau == DBNull
            return sohokhau.ToList();
        }
        public static void InsertSoHoKhau(SoHoKhauPublic _shk)
        {
            SoHoKhau shk = new SoHoKhau();
            shk.MaSHK = _shk.MaSHK;
            shk.SoSoHoKhau = _shk.SoSoHoKhau;
            shk.SoNha = _shk.SoNha;
            shk.Duong = _shk.Duong;
            shk.KhuPho = _shk.KhuPho;
            shk.CanBoDangKi = _shk.CanBoDangKi;
            shk.ChucVuNguoiCap = _shk.ChucVuNguoiCap;
            shk.NgayCap = _shk.NgayCap;
            shk.NguoiCap = _shk.NguoiCap;
            Common.db.AddToSoHoKhaus(shk);
            Common.db.SaveChanges();
        }
        public static int LoadMaSHK()
        {
            //var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select Max(MaSHK) as MaSHK from SoHoKhau ");

            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaSHK),0) as MaSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static SoHoKhauPublic LoadSoHoKhauByMaSHK(int MaSHK)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"select * from SoHoKhau where MaSHK=" + MaSHK);
            return shk.FirstOrDefault();
        }
        public static int CountSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau");
            return shk.FirstOrDefault();
        }
        public static int LoadSoSoHoKhau()
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoSHK),0) as SoSHK from SoHoKhau");
            return shk.ToList()[0];
        }
        public static int CoutSoHoKhauByYear(int firstyear, int lastyear)
        {
            var shk = Common.db.ExecuteStoreQuery<int>(@"select count(MaSHK) from SoHoKhau where YEAR(NgayCap)>=" + firstyear + "and YEAR(NgayCap)<=" + lastyear);
            return shk.FirstOrDefault();
        }
        public static int CountSoHoKhauByDay(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = "select count(MaSHK) from SoHoKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'";
            var shk = Common.db.ExecuteStoreQuery<int>(chuoi);
            return shk.FirstOrDefault();
        }
        public static string LoadDiaChiSHKByMaNK(int MaNK)
        { 
            var shk=Common.db.ExecuteStoreQuery<string>("select N'Số '+SoNha+N', Đường '+Duong+N', Khu Phố/Ấp '+CAST(KhuPho as nvarchar(2))  as DiaChi  from SoHoKhau where MaSHK in (select MaSHK from Nhankhau where MaNK="+MaNK+")");
            return shk.FirstOrDefault();
        }
        public static void UpdateSHK(string SoNha, int Ap, string Duong, int NguoiCap,int SoSoHoKhau,int ChucVu)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(@"update SoHoKhau set SoNha=N'" + SoNha + "',Duong=N'" + Duong + "',KhuPho=" + Ap + ",NguoiCap=" + NguoiCap + ",ChucVuNguoiCap="+ChucVu+"where SoSoHoKhau=" + SoSoHoKhau);
            Common.db.SaveChanges();
        }
        public static void DeleteSHK(int SoSoHoKhau)
        {
            var shk = Common.db.ExecuteStoreQuery<NhanKhauPublic>("delete from SoHoKhau where SoSoHoKhau=" + SoSoHoKhau);
            Common.db.SaveChanges();
        }
        public static int CountSHKDeleteByDay(DateTime batdau, DateTime ketthuc)
        {
            var shk = Common.db.ExecuteStoreQuery<int>("select count(MaSHK) from SoHoKhau where NgayXoa>='" + batdau + "'and NgayXoa<='" + ketthuc + "'");
            return shk.FirstOrDefault();
        }
        public static List<SoHoKhauPublic> ChiTietSHKByDay(DateTime batdau, DateTime ketthuc)
        {
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>("select MaSHK from SoHoKhau where NgayCap>='" + batdau + "'and NgayCap<='" + ketthuc + "'");
            return shk.ToList();
        }
        public static void UpdateNgayXoa(int MaSHK, DateTime NgayXoa)
        {
            string chuoi = "update SoHoKhau set NgayXoa='" + NgayXoa + "' where MaSHK=" + MaSHK;
            var shk = Common.db.ExecuteStoreQuery<NhanKhauPublic>(chuoi);
            Common.db.SaveChanges();
        }
        public static List<SoHoKhauPublic> BaoCaoSHK(DateTime batdau, DateTime ketthuc)
        {
            string chuoi = @"select SoSoHoKhau,(select HoTen from NhanKhau where MaSHK=SoSoHoKhau and MaQH=1) as ChuHo,
					(select COUNT(MaNK) FROM Nhankhau where MaSHK=SoSoHoKhau) as NhanKhau,	
					(select COUNT(MaNK) from Nhankhau nk where GioiTinh=1 and nk.MaSHK=SoSoHoKhau) as SoNu,
					(select COUNT(MaNK) from Nhankhau nk where DATEDIFF(YYYY,NgaySinh,GETDATE())>14 and nk.MaSHK=SoSoHoKhau) as Tuoi14,
					cast(SoNha as varchar(8))+', '+Duong+', '+cast(KhuPho as varchar(2)) as DiaChi," +
                    "cast('" + batdau + "' as DateTime) as BatDau, cast('" + ketthuc + "' as DateTime) as KetThuc" +
					 " from SoHoKhau where NgayCap>='"+batdau+"' and NgayCap<='"+ketthuc + "'";
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(chuoi);
            return shk.ToList();
        }
        public static SoHoKhauPublic LoadSHKByChuHo(string ChuHo)
        {
            string chuoi = @"select shk.SoNha,shk.Duong,shk.KhuPho from SoHoKhau shk, Nhankhau nk where shk.MaSHK=nk.MaSHK and nk.MaQH=1 and nk.HoTen=N'" + ChuHo+"'";
            var shk = Common.db.ExecuteStoreQuery<SoHoKhauPublic>(chuoi);
            return shk.FirstOrDefault();
        }
    }
}
